Normalization of Relations with Nulls in Candidate Keys

This paper discusses normalization of relations when the candidate keys of a relation have missing information represented by nulls. The paper shows that when the missing information is of the type " not applicable " or " does not exist, " problems and confusion can arise in normalizing relations. Candidate keys with missing information commonly are found in relations that represent information on two entities with a one-to-one relationship between them. The current definition of Boyce-Codd Normal Form (BCNF) is ineffective in identifying poor designs in such relations that may have insertion/deletion anomalies. It is shown that the above problem can be corrected by incorporating the concept of entity integrity rule into the definition of BCNF. This paper also shows that incorporating the entity integrity rule into the definition of either a relation or a candidate key does not provide a satisfactory solution to the problem. The relational database design concepts were developed without considering missing information in relations Value of an attribute in a tuple may be missing for several reasons: 1) Value is applicable but it is unknown, 2) Value is not applicable, 3) Value does not exist, or 4) Other reasons such as value is undefined (Date, 2000). An example of value that is not applicable is the attribute driver license number for a ten-year-old child. If an adult does not have a driver's license number, then the value does not exist. If an adult has a driver's license number, but it is unknown, then the value is applicable but unknown.